package DBLayer;

import java.sql.*;
import java.util.ArrayList;

import ModelLayer.BankAccount;

public class DBBankAccount implements IFDBBankAccount {
	
	private Connection con = DBConnection.getInstance().getDBcon();
	private static DBBankAccount dbBank;
	
	private DBBankAccount(){}
	
	public static DBBankAccount getInstance(){
		if(dbBank==null) dbBank = new DBBankAccount();
		return dbBank;
	}
	
	@Override
	public ArrayList<BankAccount> getAll() {
		return miscWhere("");
	}

	@Override
	public BankAccount getBankAccount(String accountNo) {
		return singleWhere("accountNo = '"+accountNo+"'");
	}
	
	@Override
	public BankAccount getBankAccount_byAccountNo(String accountNo) {
		return singleWhere("accountNo LIKE '"+accountNo+"%'");
	}
	
	@Override
	public BankAccount getBankAccount_byBankName(String bankName) {
		return singleWhere("bankName LIKE '"+bankName+"%'");
	}
	
	@Override
	public BankAccount getBankAccount_byBranchCode(String branchCode) {
		return singleWhere("ba.branchCode LIKE '"+branchCode+"%'");
	}
	
	@Override
	public BankAccount getBankAccount_byAccountName(String accountName) {
		return singleWhere("accountName LIKE '"+accountName+"%'");
	}

	@Override
	public int insert(BankAccount bankAcc) 
	{
		int rc = -1;
		
		String query2 = "INSERT INTO BankDetails (branchCode,bankName) VALUES ('"+bankAcc.getBranchCode()+"','"+bankAcc.getBankName()+"')";
		String query4 = "INSERT INTO BankAccount (accountNo,accountName,branchCode) VALUES ('"+bankAcc.getAccountNo()+"','"+bankAcc.getAccountName()+"','"+bankAcc.getBranchCode()+"')";
		
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			
			rc = updateBankDetails(bankAcc.getBranchCode(), bankAcc.getBankName());
			if(rc == 0) rc = stmt.executeUpdate(query2);
			
			rc = updateBankAccount(bankAcc.getAccountNo(),bankAcc.getAccountName(),bankAcc.getBranchCode());
			if(rc == 0) rc = stmt.executeUpdate(query4);			
			
			
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not insert the bank account  "+e.getMessage());
		}
		
		
		return rc;
	}
	
	
	@Override
	public int update(BankAccount bankAcc) 
	{
		int rc = -1;
		
		rc = updateBankDetails(bankAcc.getBranchCode(), bankAcc.getBankName());
		if(rc!=-1) rc = updateBankAccount(bankAcc.getAccountNo(),bankAcc.getAccountName(),bankAcc.getBranchCode());
		
		return rc;
	}
	
	
	
	public int updateBankDetails(String branchCode, String bankName) 
	{
		int rc = -1;
		String query = "UPDATE BankDetails SET bankName='"+bankName+"' WHERE branchCode='"+branchCode+"'";
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not update the bank details. "+e.getMessage());
		}
		
		//System.out.println("Bank Details update branhCode:"+branchCode+"; RC"+rc);
		
		return rc;
	}
	
	public int updateBankAccount(String accountNo, String accountName, String branchCode) 
	{
		int rc = -1;
		String query = "UPDATE BankAccount SET accountName='"+accountName+"',branchCode='"+branchCode+"' WHERE accountNo='"+accountNo+"'";
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not update the bank account. "+e.getMessage());
		}
		
		//System.out.println("Bank Account update accountNo:"+accountNo+"; RC"+rc);
		return rc;
	}
	
	
	
	

	@Override
	public int delete(String accountNo) {
		int rc = -1;
		String query = "DELETE FROM BankAccount WHERE bankAccount='"+accountNo+"'";
		
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not delete the bank account "+e.getMessage());
		}
		
		return rc;
	}
	
	
	
	
	
	private BankAccount singleWhere(String wClause){
		BankAccount bankAccount = new BankAccount();
		
		ResultSet result;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			result = stmt.executeQuery(query);
			
			if(result.next()){
				bankAccount = buildBankAccount(result);
			}
			
			stmt.close();
			
		}
		catch(Exception e){
			System.err.println("Could not execute select bank account query "+e.getMessage());
		}
		
		return bankAccount;
	}
	
	
	private ArrayList<BankAccount> miscWhere(String wClause){
		ArrayList<BankAccount> bankAccounts = new ArrayList<>();
		
		ResultSet results;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			while(results.next())
			{
				bankAccounts.add(buildBankAccount(results));
			}
			
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not execute multiple select bank account query "+e.getMessage());
		}
		
		return bankAccounts;
	}
	
	
	private String buildQuery(String wClause){
		String query = "SELECT accountNo,accountName,ba.branchCode,bankName FROM BankAccount ba INNER JOIN BankDetails bd ON ba.branchCode = bd.branchCode";
		
		if(wClause.length()>0) query += " WHERE "+wClause;
		
		return query;
	}
	
	
	private BankAccount buildBankAccount(ResultSet result){
		BankAccount bankAccount = new BankAccount();
		
		try{
			bankAccount.setAccountNo(result.getString("accountNo"));
			bankAccount.setAccountName(result.getString("accountName"));
			bankAccount.setBranchCode(result.getString("branchCode"));
			bankAccount.setBankName(result.getString("bankName"));
		}
		catch(Exception e){
			System.err.println("Could not build the BankAccount Result "+e.getMessage());
		}
		
		return bankAccount;
	}
	
	
	
}
